﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Globalization;
using System.Collections.Specialized;
using System.Collections;

//comm -- / <summary>
//comm -- Provides interface to the abstract db layer 
//comm -- / </summary>
namespace nsDb
    {
    public class MetaDataBaseControl
        {

					private string _ConnectionString;
					public string ConnectionString
					{
						get { return _ConnectionString; }
						set { _ConnectionString = value; }
					} //eof property FieldName 

					public MetaDataBaseControl ( string connectionStr )
					{
						this.ConnectionString = connectionStr; 
					} //eof constructor 


        public bool GetTableMetaData ( ref string msg, ref DataSet ds, ref string tableName , int visible )
            {
            //debugOk Utils.Debugger.WriteIf ( "MetaDbControl.cs GetTableMetaData START" );
            string lastPart = "";
            int vidim;
            if (visible == 1 || visible == 0 )
                {
                vidim = System.Convert.ToInt16 ( visible );
                lastPart = " AND CAST([value]  AS VARCHAR(500)) like '%visible=\"" +
                     vidim + "\"%'";
                }
            else
            {
								lastPart = String.Empty;
						}
            
            try
                {
                using (Database db = new Database( _ConnectionString))
                using (DbTransaction txn = db.BeginTransaction ( ))
                    {

                    string sqlStringCommand =
                    " SELECT  c.name AS [COLUMN_NAME], sc.data_type AS [DATA_TYPE], [value] AS" +
                    " [DESCRIPTION] , c.max_length as [MAX_LENGTH] , c.is_nullable AS [OPTIONAL] " +
                    " , c.is_identity AS [IS_PRIMARY_KEY] FROM sys.extended_properties AS ep " +
                    " INNER JOIN sys.tables AS t ON ep.major_id = t.object_id " +
                    " INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id " +
                    " = c.column_id " +
                    " INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and " +
                    " c.name = sc.column_name " +
                    " WHERE class = 1 and t.name = '" + tableName + "' " + lastPart;
                    

                    //debugOk Utils.Debugger.WriteIf ("The command is " + sqlStringCommand + "\n" );
                    DbCommand cmd = db.GetSqlStringCommand ( sqlStringCommand );

                    cmd.Transaction = txn;
                    ds = db.ExecuteDataSet ( cmd );

                    txn.Commit ( );
                    
                    return true;
                    } //eof using 
                } //eof try
            catch (System.InvalidOperationException e) //comm -- occurs when no result set was found !!!
                {
                msg = "No " + tableName + " table columns with the supplied criteria were found ! \n";
                Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
                return false;
                } //eof catch (System.InvalidOperationException)

            catch (System.IndexOutOfRangeException e) //comm -- occurs when no result set was found !!!
                {
                msg = "No " + tableName + "table columns with the supplied criteria were found ! \n";
                Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
                return false;
                } //eof catch (System.IndexOutOfRangeException)

            catch (Exception e)
                {
                Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
                msg = "An error in the application occurred. Report the following error : \n " + e.Message;
                return false;
                } //eof catch
            } //eof-- public bool GetTableMetaData ( ref string msg, ref DataSet ds )



        //GetProcedureMetaData(ref msg, ref ds, procName)
        public bool GetProcedureMetaData(ref string msg, ref DataSet ds, string procName)
        {
            //debug Utils.Debugger.WriteIf("START --- GetProcedureMetaData");

            try
            {
                using (Database db = new Database(_ConnectionString))
                using (DbTransaction txn = db.BeginTransaction())
                {

                    string sqlStringCommand =@"
                    SELECT PARAMETER_NAME as 'COLUMN_NAME', DATA_TYPE ,
                    CHARACTER_MAXIMUM_LENGTH AS 'MAX_LENGTH', IS_RESULT , 
                    PARAMETER_MODE  from INFORMATION_SCHEMA.PARAMETERS 
                    WHERE SPECIFIC_NAME='" + procName + "' AND 1=1" ;


                    //debug Utils.Debugger.WriteIf ("CHECK --- GetProcedureMetaData --- The sql string is " + sqlStringCommand + "\n" );
                    DbCommand cmd = db.GetSqlStringCommand(sqlStringCommand);

                    cmd.Transaction = txn;
                    ds = db.ExecuteDataSet(cmd);

                    txn.Commit();

                    return true;
                } //eof using 
            } //eof try

            catch (Exception e)
            {
                System.Diagnostics.StackTrace st = new System.Diagnostics.StackTrace();
                string methodName = st.GetFrame(1).GetMethod().Name; 
                Utils.Debugger.WriteIf(methodName + " --- The following Exception occured : \n" + e.Message);
                msg = "An error in the application occurred. Report the following error : \n " + methodName + e.Message;
                return false;
            } //eof catch
        } //eof method public bool GetProcedureMetaData(ref string msg, ref DataSet ds, string procName)



		public bool GetItemById ( ref string msg , ref string domainName , ref DataSet rds , 
      ref string tableName , ref string itemId , ref string itemMetaId)
      {
            //Utils.Debugger.WriteIf ( "MetaDataBaseControl public bool GetItemById START" );

						string wherePart = " WHERE " + itemMetaId + " = '" + itemId + "'";

						if (itemId.Equals ( String.Empty ) || itemMetaId.Equals ( String.Empty ))
								wherePart = " WHERE 1=1 ";

            try
                {
                using (Database db = new Database( _ConnectionString))
                using (DbTransaction txn = db.BeginTransaction ( ))
                    {
                    string sqlStringCommand = "SELECT * FROM " + tableName + wherePart ;
                    DbCommand cmd = db.GetSqlStringCommand ( sqlStringCommand );
                    Utils.Debugger.WriteIf ( "The sql command string in MetaDataBaseControl GetItemById is " + sqlStringCommand );
                    cmd.Transaction = txn;
                    rds = db.ExecuteDataSet ( cmd );

                    txn.Commit ( );

                    //NOW START THE RECORDING FOR THE LINK FUNTIONALITY
                    //create the new sqling
                    //debugOk //Utils.Debugger.DebugDataSet ( "from the GetItemById in THE RETURN DATASET !!! MetaDbControl.cs", ref rds );		
                    return true;
                    } //eof using


                } //eof try
            catch (System.InvalidOperationException e) //comm -- occurs when no result set was found !!!
                {
                msg = "No items with the supplied criteria were found ! \n";
                Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
                return false;
                } //eof catch (System.InvalidOperationException)

            catch (System.IndexOutOfRangeException e) //comm -- occurs when no result set was found !!!
                {
                msg = "No items with the supplied criteria were found ! \n";
                Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
                return false;
                } //eof catch (System.IndexOutOfRangeException)

            catch (Exception e)
                {
                msg = "No items with the supplied criteria were found ! \n";
                Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
                return false ;  //comm -- returns false
                } //eof catch catch (Exception e)

            } //eof public bool GetItemById 





			



		public bool ListMetaDataColumn ( ref string msg, ref string typeOfObject, ref DataSet ds )
		{
			//check for SQL Injection 
			if ( Utils.InputValidator.IsValidInput ( ref msg, typeOfObject ) != true )
				return false;

			try
			{

				using ( Database db = new Database ( _ConnectionString ) )
				using ( DbTransaction txn = db.BeginTransaction ( ) )
				{

					string sqlStringCommand = "SELECT [NAME] AS [COLUMN_NAME] FROM " + 
						typeOfObject + " ORDER BY [NAME] ASC";

					//debugOk Utils.Debugger.WriteIf ( "The command is " + sqlStringCommand + "\n" );
					DbCommand cmd = db.GetSqlStringCommand ( sqlStringCommand );

					cmd.Transaction = txn;
					ds = db.ExecuteDataSet ( cmd );
					txn.Commit ( );
					//remove the @s    
					Utils.Data.DataSetGastArbeiter.ChopOffAts ( ref msg, ref ds );
				} //eof using
				return true;
			} //eof try
			catch ( System.InvalidOperationException e ) //comm -- occurs when no result set was found !!!
			{
				msg = "No " + typeOfObject + " table columns with the supplied criteria were found ! \n";
				Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
				return false;
			} //eof catch (System.InvalidOperationException)

			catch ( System.IndexOutOfRangeException e ) //comm -- occurs when no result set was found !!!
			{
				msg = "No " + typeOfObject + "table columns with the supplied criteria were found ! \n";
				Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
				return false;
			} //eof catch (System.IndexOutOfRangeException)

			catch ( Exception e )
			{
				Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
				msg = "An error in the application occurred. Report the following error : \n " + e.Message;
				return false;
			} //eof catch
		} //eof-- public bool GetTableMetaData ( ref string msg, ref DataSet ds )


								
				//public bool GetAllFromTableOrView ( ref msg , ref domainName , ref rds , ref tableName  )
				public bool GetAllFromTableOrView ( ref string msg , ref string domainName , ref DataSet rds ,
																				ref string tableName  )
				{
						Utils.Debugger.WriteIf ( "MetaDataBaseControl public bool GetAllFromTableOrView START" );
						try
						{
								using (Database db = new Database( _ConnectionString))
								using (DbTransaction txn = db.BeginTransaction ())
								{
										////comm - check input
										//if (Utils.InputValidator.IsValidInput ( ref msg , tableName ))
										//    return false;

										string sqlStringCommand = "SELECT * FROM " + tableName + " WHERE 1=1";
										DbCommand cmd = db.GetSqlStringCommand ( sqlStringCommand );
										//debugOk Utils.Debugger.WriteIf ( "The sql command string in MetaDataBaseControl GetItemById is " + sqlStringCommand );
										cmd.Transaction = txn;
										rds = db.ExecuteDataSet ( cmd );

										txn.Commit ();
										if (rds == null)
										{		msg= "No values matched your criteria";
												return false;
										}
										return true;
								} //eof using


						} //eof try
						catch (System.InvalidOperationException e) //comm -- occurs when no result set was found !!!
						{
								msg = "No items with the supplied criteria were found ! \n";
								Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
								return false;
						} //eof catch (System.InvalidOperationException)

						catch (System.IndexOutOfRangeException e) //comm -- occurs when no result set was found !!!
						{
								msg = "No items with the supplied criteria were found ! \n";
								Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
								return false;
						} //eof catch (System.IndexOutOfRangeException)

						catch (Exception e)
						{
								msg = "No items with the supplied criteria were found ! \n";
								Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
								return false;  //comm -- returns false
						} //eof catch catch (Exception e)

				} //eof public bool GetItemById 


				//GetAllFromTableOrViewWhere ( ref msg, ref domainName, ref rds, ref tableName , ref ldic)
				public bool GetAllFromTableOrViewWhere ( ref string msg, ref string domainName, ref DataSet rds, ref string tableName , ref ListDictionary ldic)
				{
					Utils.Debugger.WriteIf ( "MetaDataBaseControl public bool GetAllFromTableOrView START" );
					try
					{
						using ( Database db = new Database ( _ConnectionString ) )
						using ( DbTransaction txn = db.BeginTransaction ( ) )
						{


							string sqlStringCommand = "SELECT * FROM " + tableName + " WHERE " ;


						String [] myKeys  = new String [ ldic.Count ] ; 
						ldic.Keys.CopyTo ( myKeys , 0 ) ;


							for ( int i = 0 ; i< ldic.Count ; i++ )
							{
								
								sqlStringCommand = sqlStringCommand + 
									Convert.ToString( myKeys [i] ) + 
									" = '" + Convert.ToString( ldic [ myKeys [i] ]  ) + "'";
												
							} //eof for

							DbCommand cmd = db.GetSqlStringCommand ( sqlStringCommand );
							//debugOk Utils.Debugger.WriteIf ( "The sql command string in MetaDataBaseControl GetItemById is " + sqlStringCommand );
							cmd.Transaction = txn;
							rds = db.ExecuteDataSet ( cmd );

							txn.Commit ( );
							if ( rds == null )
							{
								msg= "No values matched your criteria";
								return false;
							}
							return true;
						} //eof using


					} //eof try
					catch ( System.InvalidOperationException e ) //comm -- occurs when no result set was found !!!
					{
						msg = "No items with the supplied criteria were found ! \n";
						Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message + 
							"from GetAllFromTableOrViewWhere") ;
						return false;
					} //eof catch (System.InvalidOperationException)

					catch ( System.IndexOutOfRangeException e ) //comm -- occurs when no result set was found !!!
					{
						msg = "No items with the supplied criteria were found ! \n";
						Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message + 
							"GetAllFromTableOrViewWhere" );
						return false;
					} //eof catch (System.IndexOutOfRangeException)

					catch ( Exception e )
					{
						msg = "No items with the supplied criteria were found ! \n";
						Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message + 
							" GetAllFromTableOrViewWhere" );
						return false;  //comm -- returns false
					} //eof catch catch (Exception e)

				} //eof public bool GetItemById 



				//public bool GetAllFromTableOrView ( ref msg , ref domainName , ref rds , ref tableName  , ref orderByItem)
				public bool GetAllFromTableOrViewOrderBy ( ref string msg , ref string domainName , ref DataSet rds ,
																				ref string tableName , ref string orderByItem)
				{
						Utils.Debugger.WriteIf ( "MetaDataBaseControl public bool GetAllFromTableOrView START" );
						try
						{
								using (Database db = new Database( _ConnectionString))
								using (DbTransaction txn = db.BeginTransaction ())
								{
										////comm - check input
										//if (Utils.InputValidator.IsValidInput ( ref msg , tableName ))
										//    return false;

										string sqlStringCommand = "SELECT * FROM " + tableName + " WHERE 1=1 ORDER BY "+ orderByItem;
										Utils.Debugger.WriteIf ( "From GetAllFromTableOrViewOrderBy sqlStringCommand is " + 
												sqlStringCommand.ToString () );
										DbCommand cmd = db.GetSqlStringCommand ( sqlStringCommand );
										//debugOk Utils.Debugger.WriteIf ( "The sql command string in MetaDataBaseControl GetItemById is " + sqlStringCommand );
										cmd.Transaction = txn;
										rds = db.ExecuteDataSet ( cmd );

										txn.Commit ();
										if (rds == null)
										{
												msg= "No values matched your criteria";
												return false;
										}
										return true;
								} //eof using


						} //eof try
						catch (System.InvalidOperationException e) //comm -- occurs when no result set was found !!!
						{
								msg = "No items with the supplied criteria were found ! \n";
								Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
								return false;
						} //eof catch (System.InvalidOperationException)

						catch (System.IndexOutOfRangeException e) //comm -- occurs when no result set was found !!!
						{
								msg = "No items with the supplied criteria were found ! \n";
								Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
								return false;
						} //eof catch (System.IndexOutOfRangeException)

						catch (Exception e)
						{
								msg = "No items with the supplied criteria were found ! \n";
								Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
								return false;  //comm -- returns false
						} //eof catch catch (Exception e)

				} //eof public bool GetItemById 
						



				//callMe InsertDynNoId ( ref domainName , ref ds , ref msg , ref tableName  )
				public bool InsertDynNoId ( ref string domainName , ref DataSet ds , ref string msg , ref string tableName )
				{
						Utils.Debugger.WriteIf ( "MetaDataBaseControl.InsertDynNoId called" );
						//Utils.Debugger.DebugDataSet ( "from MetaDataBaseControl - InsertDynNoId - ref ds" , ref ds );
						

						
						try
						{
								//Utils.Debugger.DebugDataSet ( "from MetaDataBaseControl -- UpdateDynamicById  ref ds--- " , ref ds );
								using (Database db = new Database( _ConnectionString))
								using (DbTransaction txn = db.BeginTransaction ())
								{
										string sqlStringCommand = "INSERT INTO " + tableName +  " (";
										string valuesPart = " ) VALUES (";

										//trop_InlineEditCycle -- cycle trough the parameter dataset and add the ones with include == true
										foreach (DataTable dt in ds.Tables) //comm -- we have only one table
										{
												foreach (DataRow dr in dt.Rows)
												{
														//debugOk Utils.Debugger.WriteIf ( "My sqlStringCommand IS " + sqlStringCommand.ToString () );
														//if we have some value in the textfield check also if it is valid
														if (( (string) ( dr [ "filledValue" ] ) ).Length >0 && 
						                                    Utils.InputValidator.IsValidInput ( ref msg , (string) ( dr [ "filledValue" ] ) ))
														{
																//this is for the columnName is null syntax difference = not used !!!
																if (System.Convert.ToString ( dr [ "filledValue" ] ).Equals ( "NULL" ) == false)
																{
																		if (System.Convert.ToBoolean ( dr [ "IS_PRIMARY_KEY" ] ) == true)
																		{
																				; //comm - identity columns cannot be INSERTED
																		}
																		else
																		{
																				//comm - IF THE DATATYPE IS NUMERIC REPLACE THE , WITH .
																				if (System.Convert.ToString ( dr [ "DATA_TYPE" ] ).Equals ( "decimal" ))
																				{
																						Regex Remover = new Regex ( @"^(.*)\,(.*)$" ,
																														RegexOptions.IgnoreCase | RegexOptions.Compiled );
																						dr [ "filledValue" ] = Remover.Replace ( System.Convert.ToString ( dr [ "filledValue" ] ) , "$1.$2" );
																						//debugOk Utils.Debugger.WriteIf ( "My new value is " + dr [ "filledValue" ] );
																				} //eof if this is decimal type
																				else if (System.Convert.ToString ( dr [ "DATA_TYPE" ] ).Equals ( "datetime" ))
																				{
																						dr [ "filledValue" ] =  Utils.Str.Formatter.FromObjToLongDateString ( dr [ "filledValue" ] );
																				} //eof if it is datetime
																				else if (System.Convert.ToString ( dr [ "DATA_TYPE" ] ).Equals ( "smalldatetime" )
																						)
																				{
																						dr [ "filledValue" ] = Utils.Str.Formatter.FromObjToShortDateString ( dr [ "filledValue" ] );
																				} //eof if it is smalldatetime
																				else { ; }

																				sqlStringCommand = sqlStringCommand + (string) dr [ "COLUMN_NAME" ] + " , " ;
																				valuesPart = valuesPart + 
						                                Utils.Str.Str.Sanitize ( System.Convert.ToString ( dr [ "filledValue" ] ).Trim () , "'" ) + " , ";
																		}
																} //eof if the value is not null [Status] = N'History',
																else if (System.Convert.ToString ( dr [ "filledValue" ] ).Trim ().Equals ( String.Empty ) == true)
																{
																		//do nothing = do not update NULL values
																}
														} //eof if filledValue.Value > 0 and isValidInput

														//comm if this is the primary key used in the where clause
														if (System.Convert.ToBoolean ( dr [ "IS_PRIMARY_KEY" ] ) == true)
														{
																//do nothing we expect proper db design -- e.g. id's are db generated 
														}
														Utils.Debugger.WriteIf ( "My sqlStringCommand IS " + sqlStringCommand.ToString () );
												}	//eof foreach (DataTable dt in ds.Tables)
										}	//eof foreach (DataRow dr in dt.Rows)
										//comm -- now chop the last 3 characters - the obsolet " , "

										char [] charsToTrimAtTheEndFromSQLC= { ' ' , ',' , ' ' };
										sqlStringCommand = sqlStringCommand.TrimEnd ( charsToTrimAtTheEndFromSQLC );
										valuesPart = valuesPart.TrimEnd ( charsToTrimAtTheEndFromSQLC ) + ")" ;
										sqlStringCommand = sqlStringCommand + valuesPart;
										
										Utils.Debugger.WriteIf ( "from MetaDataBaseControl InsertDynNoId -- " + sqlStringCommand + "\n" );
										DbCommand cmd = db.GetSqlStringCommand ( sqlStringCommand );
										cmd.Transaction = txn;
										int ret = db.ExecuteNonQuery ( cmd , txn );
										txn.Commit ();
										return Convert.ToBoolean ( ret ) ;
								} //eof using


						} //eof try
						catch (System.InvalidOperationException e) //comm -- occurs when no result set was found !!!
						{
								msg = "No items with the supplied criteria were found ! \n";
								Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
								return false;
						} //eof catch (System.InvalidOperationException)

						catch (System.IndexOutOfRangeException e) //comm -- occurs when no result set was found !!!
						{
								msg = "No items with the supplied criteria were found ! \n";
								Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
								return false;
						} //eof catch (System.IndexOutOfRangeException)

						catch (Exception e)
						{
								msg = "No items with the supplied criteria were found ! \n";
								Utils.Debugger.WriteIf ( "The following Exception occured : \n" + e.Message );
								return false;  //comm -- returns false
						} //eof catch

				} //eof method UpdateDynamicById

//GetTableNameColumnName(ref msg, ref ds , tableName )
public bool GetTableNameColumnName(ref string msg, ref DataSet ds , string tableName )
{

    try
    {
        using (Database db = new Database(_ConnectionString))
        using (DbTransaction txn = db.BeginTransaction())
        {
            string partWhere = " WHERE ";
            if (tableName == null || tableName.Equals(String.Empty))
                partWhere = partWhere + "1=1 ";
            else
                partWhere = partWhere + " TABLE_NAME = '" + tableName + "'";

            string sqlStringCommand = "select COLUMN_NAME , TABLE_NAME from INFORMATION_SCHEMA.COLUMNS " + partWhere; 



            //debugOk Utils.Debugger.WriteIf ("The command is " + sqlStringCommand + "\n" );
            DbCommand cmd = db.GetSqlStringCommand(sqlStringCommand);

            cmd.Transaction = txn;
            ds = db.ExecuteDataSet(cmd);

            txn.Commit();

            if (ds != null)
                return true;
            else
                return false; 

        } //eof using
    } //eof try 
    catch (Exception ex)
    {
        System.Diagnostics.StackTrace st = new System.Diagnostics.StackTrace();
        string methodName = st.GetFrame(1).GetMethod().Name;
        Utils.Debugger.WriteIf(methodName + " --- The following Exception occured : \n" + ex.Message);
        return false; 

    } //eof catch



} //eof method GetTableNameColumnName




 public bool CopyPasteMe(ref string msg, ref DataSet ds , string procName )
{

    try
    {
        using (Database db = new Database(_ConnectionString))
        using (DbTransaction txn = db.BeginTransaction())
        {
            

            return true;
        } //eof using 
    } //eof try 
    catch ( Exception e ) 
    {
        System.Diagnostics.StackTrace st = new System.Diagnostics.StackTrace();
        string methodName = st.GetFrame(1).GetMethod().Name;
        Utils.Debugger.WriteIf(methodName + " --- GenProcMetaData --- The following Exception occured : \n" + e.Message);
        msg = "Failed to regtrieve data for " + procName;
        return false; 

    } //eof catch
        } //eof method

    
    
    
    
    } //eof class MetaDataBaseControl
} //eof nsDb

